package com.boss.salary.dao.impl;

import com.boss.framework.dao.BaseDaoImpl;
import com.boss.framework.dto.PaginationDTO;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.ISalaryGrantSignDao;
import com.boss.salary.entity.SalaryGrantSign;
import com.boss.sys.utils.SysUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

@Repository
public class SalaryGrantSignDaoImpl extends BaseDaoImpl implements ISalaryGrantSignDao {

    @Override
    public List<SalaryGrantSign> getSalaryGrantSign(PaginationDTO paginationDTO, Map<String, Object> queryParam) {
        StringBuilder sql = new StringBuilder();
        List<Object> param= new ArrayList<>();
        sql.append(" select  pe.sala_card_bank, ai.agency_code, ai.agency_name,")
           .append(" sum( case when pi.state = 3 then 1 else 0 end )personEdit,")
           .append(" COUNT(CASE WHEN pi.state = 3  THEN 1  ELSE NULL  END)-AVG(syxx.zzlastmonth) personEditLastMonth," )
           .append(" COUNT(CASE  WHEN pi.state = 1 THEN 1 ELSE NULL  END)-AVG(syxx.lxlastmonth) personRetireLastMonth, ")
           .append(" sum(case when pi.state = 1 then 1 else 0 end )personRetire,")
           .append(" sum(case when pi.state = 3 then nvl(sme.s1,0) else 0 end) realEdit,")
           .append(" (sum(case when pi.state = 3 then nvl(sme.s1,0) else 0 end)-avg(syxx.sysfzb))realEditLastMonth, ")
           .append(" sum(case when pi.state = 1 then nvl(sme.s1,0) else 0 end) realRetire, ")
           .append(" (sum(case when pi.state = 1 then nvl(sme.s1,0) else 0 end)-avg(syxx.sysflx))realRetireLastMonth,")
           .append(" sum(case when pi.state = 3 then nvl(sme.s2,0) else 0 end) shouldCareer,")
           .append(" (sum(case when pi.state = 3 then nvl(sme.s2,0) else 0 end)-avg(syxx.syyfsy))shouldCareerLastMonth,")
           .append(" sum(case when pi.state = 1 then nvl(sme.s2,0) else 0 end)shouldRetire,")
           .append(" (sum(case when pi.state = 1 then nvl(sme.s2,0) else 0 end)-avg(syxx.syyflx))shouldRetireLastMonth ")

           .append(" from ").append(TableName.AGENCY_INFO).append(" as ai ")
           .append(" join ").append(TableName.PERSON_EXT).append(" as pe on pe.agency_id=ai.id")
           .append(" join ").append(TableName.PERSON_INFO).append(" as pi on pi.id=pe.person_id  and pi.is_deleted=0 ")
           .append(" join ").append(TableName.SALARY_MONTH_END).append(" as sme on sme.person_id=pe.person_id")
           .append(" join ").append(TableName.AGENCY_VERIFY).append(" as av on av.agency_id=ai.id and av.is_month_end=0")
           .append(" left join (").append(" select ai.agency_code agency_code,")
                .append(" COUNT(CASE   WHEN pi.state = 3   THEN 1      ELSE NULL     END) zzlastmonth, ")
                .append(" COUNT(CASE   WHEN pi.state = 1   THEN 1      ELSE NULL     END) lxlastmonth,")
                 .append(" sum(case when pi.state = 3 then nvl(sh.s1,0) else 0 end)sysfzb,")
                 .append(" sum(case when pi.state = 1 then nvl(sh.s1,0) else 0 end)sysflx,")
                 .append(" sum(case when pi.state = 3 then nvl(sh.s2,0) else 0 end)syyfsy,")
                 .append(" sum(case when pi.state = 1 then nvl(sh.s2,0) else 0 end)syyflx ")
                 .append(" from ").append(TableName.AGENCY_INFO).append(" as ai ")
                 .append(" join ").append(TableName.PERSON_EXT).append(" as pe on pe.agency_id = ai.id")
                 .append(" join ").append(TableName.PERSON_INFO).append(" as pi on pi.id=pe.person_id")
                 .append(" join ").append(TableName.SALARY_HISTORY).append(" as sh on sh.person_id = pe.person_id")
                 .append(" where sh.salary_month=( select max(salary_month) from ").append(TableName.SALARY_HISTORY).append(")")
                .append(" group by ai.agency_code ")
           .append(" ) syxx on syxx.agency_code=ai.agency_code")
           .append(" where  pe.sala_card_bank is not NULL and pe.sala_card_bank<> '' ");
        if (!StringUtils.isEmpty(queryParam.get("province"))) {
            sql.append("and ai.mof_div_code = ?");
            param.add(queryParam.get("province"));
        }
          if(!StringUtils.isEmpty(queryParam)){
              if(!StringUtils.isEmpty(queryParam.get("bddw"))){
                  if ("0".equals(queryParam.get("bddw"))) {
                      sql.append(" and av.is_salary_change = 0 ");
                  } else {
                      sql.append(" and av.is_salary_change != 0 ");
                  }
              }
              if(!StringUtils.isEmpty(queryParam.get("gzlx"))){
                  sql.append(" and pe.sala_gr_code=?");
                  param.add(queryParam.get("gzlx"));
              }
              if(!StringUtils.isEmpty(queryParam.get("salaCardBank"))){
                  String[] strs = queryParam.get("salaCardBank").toString().split(",");
                  List list = Arrays.asList(strs);
                  sql.append(" and ").append(SysUtils.inSql("pe.sala_card_bank", list));
              }
          }
        sql.append(" group by pe.sala_card_bank,ai.agency_code, ai.agency_name  ");
        sql.append(" order by  pe.sala_card_bank, ai.agency_code ");

        /*String querySql = this.boxUpSql(sql.toString());

        Object[] params = this.boxUpParam(param.toArray(), paginationDTO);*/

        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(SalaryGrantSign.class), param.toArray());
    }

    @Override
    public int getSalaryGrantSignNum(Map<String, Object> queryParam) {
        StringBuilder sql = new StringBuilder();
        List<Object> param= new ArrayList<>();
        sql.append("select count(*) from ( select  pe.sala_card_bank, ai.agency_code id, ai.agency_name,")
                .append(" sum( case when pi.state = 3 then 1 else 0 end )personEdit,")
                .append(" COUNT(CASE WHEN pi.state = 3  THEN 1  ELSE NULL  END)-AVG(syxx.zzlastmonth) personEditLastMonth," )
                .append( "COUNT(CASE  WHEN pi.state = 1 THEN 1 ELSE NULL  END)-AVG(syxx.lxlastmonth) personRetireLastMonth, ")
                .append(" sum(case when pi.state = 1 then 1 else 0 end )personRetire,")
                .append(" sum(case when pi.state = 3 then nvl(sme.s1,0) else 0 end) realEdit,")
                .append(" (sum(case when pi.state = 3 then nvl(sme.s1,0) else 0 end)-avg(syxx.sysfzb))realEditLastMonth, ")
                .append(" sum(case when pi.state = 1 then nvl(sme.s1,0) else 0 end) realRetire, ")
                .append(" (sum(case when pi.state = 1 then nvl(sme.s1,0) else 0 end)-avg(syxx.sysflx))realRetireLastMonth,")
                .append(" sum(case when pi.state = 3 then nvl(sme.s2,0) else 0 end) shouldCareer,")
                .append(" (sum(case when pi.state = 3 then nvl(sme.s2,0) else 0 end)-avg(syxx.syyfsy))shouldCareerLastMonth,")
                .append(" sum(case when pi.state = 1 then nvl(sme.s2,0) else 0 end)shouldRetire,")
                .append(" (sum(case when pi.state = 1 then nvl(sme.s2,0) else 0 end)-avg(syxx.syyflx))shouldRetireLastMonth ")

                .append(" from ").append(TableName.AGENCY_INFO).append(" as ai ")
                .append(" join ").append(TableName.PERSON_EXT).append(" as pe on pe.agency_id=ai.id")
                .append(" join ").append(TableName.PERSON_INFO).append(" as pi on pi.id=pe.person_id  and pi.is_deleted=0 ")
                .append(" join ").append(TableName.SALARY_MONTH_END).append(" as sme on sme.person_id=pe.person_id")
                .append(" join ").append(TableName.AGENCY_VERIFY).append(" as av on av.agency_id=ai.id and av.is_month_end=0")
                .append(" left join (").append(" select ai.agency_code agency_code,")
                .append("COUNT(CASE   WHEN pi.state = 3   THEN 1      ELSE NULL     END) zzlastmonth, ")
                .append("COUNT(CASE   WHEN pi.state = 1   THEN 1      ELSE NULL     END) lxlastmonth,")
                .append(" sum(case when pi.state = 3 then nvl(sh.s1,0) else 0 end)sysfzb,")
                .append(" sum(case when pi.state = 1 then nvl(sh.s1,0) else 0 end)sysflx,")
                .append(" sum(case when pi.state = 3 then nvl(sh.s2,0) else 0 end)syyfsy,")
                .append(" sum(case when pi.state = 1 then nvl(sh.s2,0) else 0 end)syyflx ")
                .append(" from ").append(TableName.AGENCY_INFO).append(" as ai ")
                .append(" join ").append(TableName.PERSON_EXT).append(" as pe on pe.agency_id = ai.id")
                .append(" join ").append(TableName.PERSON_INFO).append(" as pi on pi.id=pe.person_id")
                .append(" join ").append(TableName.SALARY_HISTORY).append(" as sh on sh.person_id = pe.person_id")
                .append(" where sh.salary_month=( select max(salary_month) from ").append(TableName.SALARY_HISTORY).append(")")
                .append(" group by ai.agency_code ")
                .append(" ) syxx on syxx.agency_code=ai.agency_code")
                .append(" where  pe.sala_card_bank is not NULL and pe.sala_card_bank<> ''");
           if(!StringUtils.isEmpty(queryParam)){
            if(!StringUtils.isEmpty(queryParam.get("bddw"))){
                if ("0".equals(queryParam.get("bddw"))) {
                    sql.append(" and av.is_salary_change = 0 ");
                } else {
                    sql.append(" and av.is_salary_change != 0 ");
                }
            }
            if(!StringUtils.isEmpty(queryParam.get("gzlx"))){
                sql.append(" and pe.sala_gr_code=?");
                param.add(queryParam.get("gzlx"));
            }

        }
                sql.append(" group by pe.sala_card_bank,ai.agency_code, ai.agency_name) a ");

        return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class,param.toArray());
    }

    @Override
    public SalaryGrantSign getSalaryGrantSignCombined(Map<String, Object> queryParam) {
        StringBuilder sql = new StringBuilder();
        List<Object> param= new ArrayList<>();
        sql.append(" select sum(personEdit)personEdit, ")
                .append(" sum(personEdit)-(select sum(case when spi.state=3 then 1 else 0 end)")
                .append(" from ").append(TableName.PERSON_INFO).append(" as spi")
                .append(" join (select agency_code ,person_id from sal_person_history where salary_month = (")
                .append("  select MAX(salary_month) from sal_person_history where is_deleted=0 ) and is_deleted=0")
                .append(" )a on a.person_id=spi.id) personEditLastMonth,")
                .append(" sum(personRetire)-(select sum(case when spi.state=1 then 1 else 0 end) ")
                .append(" from ").append(TableName.PERSON_INFO).append(" as spi")
                .append(" join (select agency_code ,person_id from sal_person_history where salary_month = (")
                .append("  select MAX(salary_month) from sal_person_history where is_deleted=0 ) and is_deleted=0")
                .append(" )a on a.person_id=spi.id) personRetireLastMonth,")

                .append(" sum(personRetire)personRetire, sum(realEdit)realEdit,")
                .append(" sum(realEditLastMonth)realEditLastMonth, sum(realRetire)realRetire,")
                .append(" sum(realRetireLastMonth)realRetireLastMonth, sum(shouldCareer)shouldCareer,")
                .append(" sum(shouldCareerLastMonth)shouldCareerLastMonth, sum(shouldRetire)shouldRetire,")
                .append(" sum(shouldRetireLastMonth)shouldRetireLastMonth from ")
                .append("( select  pe.sala_card_bank, ai.agency_code id, ai.agency_name,")
                .append(" sum( case when pi.state = 3 then 1 else 0 end )personEdit,")
                //.append(" av.add_person_num personEditLastMonth,av.add_quit_person_num personRetireLastMonth, ")
                .append(" sum(case when pi.state = 1 then 1 else 0 end )personRetire,")
                .append(" sum(case when pi.state = 3 then nvl(sme.s1,0) else 0 end) realEdit,")
                .append(" (sum(case when pi.state = 3 then nvl(sme.s1,0) else 0 end)-avg(syxx.sysfzb))realEditLastMonth, ")
                .append(" sum(case when pi.state = 1 then nvl(sme.s1,0) else 0 end) realRetire, ")
                .append(" (sum(case when pi.state = 1 then nvl(sme.s1,0) else 0 end)-avg(syxx.sysflx))realRetireLastMonth,")
                .append(" sum(case when pi.state = 3 then nvl(sme.s2,0) else 0 end) shouldCareer,")
                .append(" (sum(case when pi.state = 3 then nvl(sme.s2,0) else 0 end)-avg(syxx.syyfsy))shouldCareerLastMonth,")
                .append(" sum(case when pi.state = 1 then nvl(sme.s2,0) else 0 end)shouldRetire,")
                .append(" (sum(case when pi.state = 1 then nvl(sme.s2,0) else 0 end)-avg(syxx.syyflx))shouldRetireLastMonth ")

                .append(" from ").append(TableName.AGENCY_INFO).append(" as ai ")
                .append(" join ").append(TableName.PERSON_EXT).append(" as pe on pe.agency_id=ai.id")
                .append(" join ").append(TableName.PERSON_INFO).append(" as pi on pi.id=pe.person_id  and pi.is_deleted=0 ")
                .append(" join ").append(TableName.SALARY_MONTH_END).append(" as sme on sme.person_id=pe.person_id")
                .append(" join ").append(TableName.AGENCY_VERIFY).append(" as av on av.agency_id=ai.id and av.is_month_end=0")
                .append(" left join (").append(" select ai.agency_code agency_code,")
                .append(" sum(case when pi.state = 3 then nvl(sh.s1,0) else 0 end)sysfzb,")
                .append(" sum(case when pi.state = 1 then nvl(sh.s1,0) else 0 end)sysflx,")
                .append(" sum(case when pi.state = 3 then nvl(sh.s2,0) else 0 end)syyfsy,")
                .append(" sum(case when pi.state = 1 then nvl(sh.s2,0) else 0 end)syyflx ")
                .append(" from ").append(TableName.AGENCY_INFO).append(" as ai ")
                .append(" join ").append(TableName.PERSON_EXT).append(" as pe on pe.agency_id = ai.id")
                .append(" join ").append(TableName.PERSON_INFO).append(" as pi on pi.id=pe.person_id ")
                .append(" join ").append(TableName.SALARY_HISTORY).append(" as sh on sh.person_id = pe.person_id")
                .append(" where sh.salary_month=( select max(salary_month) from ").append(TableName.SALARY_HISTORY).append(")")
                .append(" group by ai.agency_code ")
                .append(" ) syxx on syxx.agency_code=ai.agency_code")
                .append(" where  pe.sala_card_bank is not NULL and pe.sala_card_bank<> ''");
        if (!StringUtils.isEmpty(queryParam.get("province"))) {
            sql.append("and ai.mof_div_code = ?");
            param.add(queryParam.get("province"));
        }
        if(!StringUtils.isEmpty(queryParam)){
            if(!StringUtils.isEmpty(queryParam.get("bddw"))){
                if ("0".equals(queryParam.get("bddw"))) {
                    sql.append(" and av.is_salary_change = 0 ");
                } else {
                    sql.append(" and av.is_salary_change != 0 ");
                }
            }
            if(!StringUtils.isEmpty(queryParam.get("gzlx"))){
                sql.append(" and pe.sala_gr_code=?");
                param.add(queryParam.get("gzlx"));
            }
            if(!StringUtils.isEmpty(queryParam.get("salaCardBank"))){
                String[] strs = queryParam.get("salaCardBank").toString().split(",");
                List list = Arrays.asList(strs);
                sql.append(" and ").append(SysUtils.inSql("pe.sala_card_bank", list));
            }
        }
                sql.append("  group by pe.sala_card_bank,ai.agency_code, ai.agency_name ) a ");

        return this.getJdbcTemplate().queryForObject(sql.toString(), new BeanPropertyRowMapper<>(SalaryGrantSign.class),param.toArray());
    }

    @Override
    public List<SalaryGrantSign> getSalarySumByBank(Map<String, Object> queryParam) {
        StringBuilder sql = new StringBuilder();
        List<Object> param= new ArrayList<>();
        sql.append(" select sala_card_bank,sum(personEdit)personEdit," )
                .append(" SUM(personEditLastMonth) personEditLastMonth," )
                .append(" sum(personRetire)personRetire," )
                .append(" SUM(personRetireLastMonth)personRetireLastMonth," )
                .append( "sum(realEdit)realEdit,")
                .append(" sum(realEditLastMonth)realEditLastMonth, sum(realRetire)realRetire,")
                .append(" sum(realRetireLastMonth)realRetireLastMonth, sum(shouldCareer)shouldCareer,")
                .append(" sum(shouldCareerLastMonth)shouldCareerLastMonth, sum(shouldRetire)shouldRetire,")
                .append(" sum(shouldRetireLastMonth)shouldRetireLastMonth from ")
                .append("( select  pe.sala_card_bank, ai.agency_code id, ai.agency_name,")
                .append(" sum( case when pi.state = 3 then 1 else 0 end )personEdit,")
                .append(" COUNT(CASE WHEN pi.state = 3  THEN 1  ELSE NULL  END)-AVG(syxx.zzlastmonth) personEditLastMonth," )
                .append( "COUNT(CASE  WHEN pi.state = 1 THEN 1 ELSE NULL  END)-AVG(syxx.lxlastmonth) personRetireLastMonth, ")
                .append(" sum(case when pi.state = 1 then 1 else 0 end )personRetire,")
                .append(" sum(case when pi.state = 3 then nvl(sme.s1,0) else 0 end) realEdit,")
                .append(" (sum(case when pi.state = 3 then nvl(sme.s1,0) else 0 end)-avg(syxx.sysfzb))realEditLastMonth, ")
                .append(" sum(case when pi.state = 1 then nvl(sme.s1,0) else 0 end) realRetire, ")
                .append(" (sum(case when pi.state = 1 then nvl(sme.s1,0) else 0 end)-avg(syxx.sysflx))realRetireLastMonth,")
                .append(" sum(case when pi.state = 3 then nvl(sme.s2,0) else 0 end) shouldCareer,")
                .append(" (sum(case when pi.state = 3 then nvl(sme.s2,0) else 0 end)-avg(syxx.syyfsy))shouldCareerLastMonth,")
                .append(" sum(case when pi.state = 1 then nvl(sme.s2,0) else 0 end)shouldRetire,")
                .append(" (sum(case when pi.state = 1 then nvl(sme.s2,0) else 0 end)-avg(syxx.syyflx))shouldRetireLastMonth ")

                .append(" from ").append(TableName.AGENCY_INFO).append(" as ai ")
                .append(" join ").append(TableName.PERSON_EXT).append(" as pe on pe.agency_id=ai.id")
                .append(" join ").append(TableName.PERSON_INFO).append(" as pi on pi.id=pe.person_id  and pi.is_deleted=0 ")
                .append(" join ").append(TableName.SALARY_MONTH_END).append(" as sme on sme.person_id=pe.person_id")
                .append(" join ").append(TableName.AGENCY_VERIFY).append(" as av on av.agency_id=ai.id and av.is_month_end=0")
                .append(" left join (").append(" select ai.agency_code agency_code,")
                .append("COUNT(CASE WHEN pi.state = 3   THEN 1      ELSE NULL     END) zzlastmonth, ")
                .append("COUNT(CASE  WHEN pi.state = 1   THEN 1      ELSE NULL     END) lxlastmonth,")
                .append(" sum(case when pi.state = 3 then nvl(sh.s1,0) else 0 end)sysfzb,")
                .append(" sum(case when pi.state = 1 then nvl(sh.s1,0) else 0 end)sysflx,")
                .append(" sum(case when pi.state = 3 then nvl(sh.s2,0) else 0 end)syyfsy,")
                .append(" sum(case when pi.state = 1 then nvl(sh.s2,0) else 0 end)syyflx ")
                .append(" from ").append(TableName.AGENCY_INFO).append(" as ai ")
                .append(" join ").append(TableName.PERSON_EXT).append(" as pe on pe.agency_id = ai.id")
                .append(" join ").append(TableName.PERSON_INFO).append(" as pi on pi.id=pe.person_id")
                .append(" join ").append(TableName.SALARY_HISTORY).append(" as sh on sh.person_id = pe.person_id")
                .append(" where sh.salary_month=( select max(salary_month) from ").append(TableName.SALARY_HISTORY).append(")")
                .append(" group by ai.agency_code ")
                .append(" ) syxx on syxx.agency_code=ai.agency_code")
                .append(" where  pe.sala_card_bank is not NULL and pe.sala_card_bank<> ''");
        if (!StringUtils.isEmpty(queryParam.get("province"))) {
            sql.append("and ai.mof_div_code = ?");
            param.add(queryParam.get("province"));
        }
        if(!StringUtils.isEmpty(queryParam)){
            if(!StringUtils.isEmpty(queryParam.get("bddw"))){
                if ("0".equals(queryParam.get("bddw"))) {
                    sql.append(" and av.is_salary_change = 0 ");
                } else {
                    sql.append(" and av.is_salary_change != 0 ");
                }
            }
            if(!StringUtils.isEmpty(queryParam.get("gzlx"))){
                sql.append(" and pe.sala_gr_code=?");
                param.add(queryParam.get("gzlx"));
            }
            if(!StringUtils.isEmpty(queryParam.get("salaCardBank"))){
                String[] strs = queryParam.get("salaCardBank").toString().split(",");
                List list = Arrays.asList(strs);
                sql.append(" and ").append(SysUtils.inSql("pe.sala_card_bank", list));
            }
        }
        sql.append(" group by pe.sala_card_bank,ai.agency_code, ai.agency_name) a group by sala_card_bank");

        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(SalaryGrantSign.class), param.toArray());
    }
}
